package com.cats.bi.sqltool.basic;

import com.cats.bi.sqltool.SQLString;
import com.cats.bi.sqltool.function.Expression;
import com.cats.bi.sqltool.syntax.Having;
import com.cats.bi.sqltool.syntax.Union;
import com.cats.bi.sqltool.syntax.join.*;
import com.google.common.collect.Lists;
import lombok.Data;
import lombok.var;

import java.util.*;

@Data
public class Select extends AbstractSearchable<Select> {
    private LinkedHashSet<Object> columns = new LinkedHashSet<>();
    private LinkedList<AbstractJoin<?>> abstractJoins;
    private ArrayList<Object> groupBy;
    private AbstractClause where;
    private AbstractClause and;
    private Having having;
    private String into;
    private String[] from;
    private String expRelation = "/";
    private String subSelect;

    Object[] getColumns() {
        Object[] out = new Object[columns.size()];
        return columns.toArray(out);
    }

    public Select add(String column) {
        columns.add(column);
        return this;
    }

    public Select add(String table, String name) {
        columns.add(new Column(table, name));
        return this;
    }

    public Select add(AbstractColumn<?> column) {
        columns.add(column);
        return this;
    }

    public Select addAll(Collection<AbstractColumn<?>> columns) {
        this.columns.addAll(columns);
        return this;
    }

    public Select addAll(AbstractColumn... column) {
        Collections.addAll(columns, column);
        return this;
    }

    public Select addAll(String... column) {
        Collections.addAll(columns, column);
        return this;
    }

    public void into(String into) {
        this.into = into;
    }

    public Select from(String... from) {
        this.from = from;
        return this;
    }

    public Select subSelect(String subSelect) {
        this.subSelect = subSelect;
        return this;
    }

    private void initJoins() {
        if (abstractJoins == null) {
            abstractJoins = new LinkedList<>();
        }
    }

    public Select join(AbstractJoin<?> abstractJoin) {
        initJoins();
        abstractJoins.add(abstractJoin);
        return this;
    }

    public Select innerJoin(Column src, Column pattern) {
        initJoins();
        abstractJoins.add(new InnerJoin(src, pattern));
        return this;
    }

    public Select leftJoin(Column src, Column pattern) {
        initJoins();
        abstractJoins.add(new LeftJoin(src, pattern));
        return this;
    }

    public Select rightJoin(Column src, Column pattern) {
        initJoins();
        abstractJoins.add(new RightJoin(src, pattern));
        return this;
    }


    public Select where(AbstractClause abstractClause) {
        this.where = abstractClause;
        return this;
    }

    public Select and(AbstractClause abstractClause) {
        this.and = abstractClause;
        return this;
    }

    public Select fullJoin(Column src, Column pattern) {
        initJoins();
        abstractJoins.add(new FullJoin(src, pattern));
        return this;
    }

    public AbstractClause getWhere() {
        return where;
    }

    public AbstractClause getAnd() {
        return and;
    }

    public Select groupBy(String column) {
        if (column != null) {
            if (groupBy == null) {
                groupBy = new ArrayList<>();
            }
            groupBy.add(column);
        }
        return this;
    }

    public Select groupBy(String... columns) {
        if (columns != null) {
            if (groupBy == null) {
                groupBy = new ArrayList<>();
            }
            groupBy.addAll(Lists.newArrayList(columns));
        }
        return this;
    }

    public Select groupBy(AbstractColumn<?> column) {
        if (column != null) {
            if (groupBy == null) {
                groupBy = new ArrayList<>();
            }
            groupBy.add(column);
        }
        return this;
    }

    public Select groupBy(AbstractColumn<?>... columns) {
        if (columns != null) {
            if (groupBy == null) {
                groupBy = new ArrayList<>();
            }
            Collections.addAll(groupBy, columns);
        }
        return this;
    }

    public Select groupBy(Collection<AbstractColumn<?>> columns) {
        if (columns != null) {
            if (groupBy == null) {
                groupBy = new ArrayList<>();
            }
            groupBy.addAll(columns);
        }
        return this;
    }

    public Select having(Having having) {
        this.having = having;
//        addAll(having.getColumn());
        return this;
    }

    @Override
    public void toSql(StringBuilder sql) {
        sql.append("SELECT ");
        // columns
        columns(sql);
        into(sql);
        from(sql);
        subCountSelect(sql);
        // join on
        if (abstractJoins != null) {
            abstractJoins.forEach(join -> join.toSql(sql));
        }
        // and
        if (and != null) {
            sql.append(" and ");
            and.toSql(sql);
        }
        // where
        if (where != null) {
            sql.append(" WHERE ");
            where.toSql(sql);
        }
        // group by
        groupBy(sql);
        // having
        if (having != null) {
            having.toSql(sql);
        }
        // order by
        sort(sql);
        // limit
        limit(sql);
    }


    private void columns(StringBuilder sql) {
        boolean flag = true;
        for (Object column : columns) {
            if (flag) {
                flag = false;
            } else {
                if (column instanceof Expression) {
                    sql.append(" FROM ");
                } else {
                    sql.append(", ");
                }
            }
            if (column instanceof AbstractColumn) {
                ((AbstractColumn) column).nameInColumn(sql);
            } else {
                SQLString.appendColumn(sql, column.toString());
            }
        }
    }

    private void into(StringBuilder sql) {
        if (into != null) {
            SQLString.appendTable(sql, into);
        }
    }

    private void subCountSelect(StringBuilder sql) {

        if (subSelect != null) {
            sql.append("count(*) as count ");
            sql.append("from ( \n");
            SQLString.appendTable(sql, subSelect);
            sql.append("\n ) tmp_count");
            return;
        }
    }

    private void from(StringBuilder sql) {
        if (from != null) {
            boolean f = true;
            for (String table : from) {
                if (f) {
                    f = false;
                    sql.append(" FROM ");
//                    sql.append(" (");
                    SQLString.appendTable(sql, table);
//                    sql.append(" )");
                } else {
                    sql.append(", ");
                    SQLString.appendTable(sql, table);
                }
            }
            return;
        }
        // table
        Map<Object, AbstractTableColumn<?>> tables = new LinkedHashMap<>();
        // add all-table
        for (Object li : columns) {
            if (!(li instanceof AbstractTableColumn)) {
                continue;
            }
            AbstractTableColumn<?> column = (AbstractTableColumn) li;
            column.signTable(tables);
        }
        if (abstractJoins != null) {
            // add join-table
            for (AbstractJoin<?> abstractJoin : abstractJoins) {
                abstractJoin.column.signTable(tables);
            }
            // remove join-table
            for (AbstractJoin<?> abstractJoin : abstractJoins) {
                abstractJoin.pattern.unSignTable(tables);
            }
        }
        // from
        boolean flag = true;
        for (var table : tables.values()) {
            if (flag) {
                flag = false;
                sql.append(" FROM ");
            } else {
                sql.append(", ");
            }
            table.tableInFrom(sql);
        }
    }

    private void groupBy(StringBuilder sql) {
        if (groupBy == null) {
            return;
        }
        sql.append(" GROUP BY ");
        boolean f = true;
        for (Object li : groupBy) {
            if (f) {
                f = false;
            } else {
                sql.append(", ");
            }
            if (li instanceof AbstractColumn) {
//              ((AbstractColumn) li).shortName(sql);
                ((AbstractColumn<?>) li).toSql(sql);
            } else {
                SQLString.appendColumn(sql, li.toString());
            }
        }
//        sql.append(" WITH ROLLUP ");
    }

    @Override
    public String toString() {
        StringBuilder sql = new StringBuilder();
        toSql(sql);
        sql.append(';');
        return sql.toString();
    }

    @Override
    public Union union(Select select) {
        return new Union(this).union(select);
    }

    @Override
    public Union union(String select) {
        return new Union(this).union(select);
    }

    @Override
    public Union unionAll(Select select) {
        return new Union(this).unionAll(select);
    }

    @Override
    public Union unionAll(String select) {
        return new Union(this).unionAll(select);
    }
}